package sg.edu.nus.dbd.dao.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import sg.edu.nus.dbd.dao.DashboardDAO;
import sg.edu.nus.dbd.formbean.DetailParamData;
import sg.edu.nus.dbd.util.Constants;
import sg.edu.nus.dbd.util.DateUtil;

public class DashboardDAOImpl extends BaseDAO implements DashboardDAO {

	public double getLevel1Statistics(String paramName, Date startDate,
			Date endDate) {
		double statValue = 0;
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		if (conn != null) {
			try {

				String sqlStatement;

				if (paramName.equals(Constants.REDO_LOG_FILE_PARAM)) {
					// Redo Log File Parameter is retrieved with a different sql
					sqlStatement = "SELECT avg(a.total_waits) value FROM dba_hist_system_event a, dba_hist_snapshot b WHERE b.snap_id = a.snap_id and a.event_name ='log file parallel write' and b.end_interval_time > to_date(?, 'dd/mm/yyyy HH24:MI:SS') and b.end_interval_time < to_date(?, 'dd/mm/yyyy HH24:MI:SS')";
					ps = conn.prepareStatement(sqlStatement);
					ps.setString(1, DateUtil.getDateString(startDate));
					ps.setString(2, DateUtil.getDateString(endDate));
				} else {
					// The other parameters can be retrieved with the same sql
					// (change the id in the where clause only)
					sqlStatement = "SELECT metric_id, metric_name, avg(average) value FROM  dba_hist_sysmetric_summary WHERE metric_id =? and end_time > to_date(?, 'dd/mm/yyyy HH24:MI:SS') and end_time < to_date(?, 'dd/mm/yyyy HH24:MI:SS') GROUP BY metric_id, metric_name";
					ps = conn.prepareStatement(sqlStatement);
					String metricID = (String) Constants.DASHBOARD_PARAMS
							.get(paramName);
					ps.setString(1, metricID);
					ps.setString(2, DateUtil.getDateString(startDate));
					ps.setString(3, DateUtil.getDateString(endDate));
				}

				rs = ps.executeQuery();
				if (rs.next()) {
					statValue = rs.getDouble("value");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeStatement(ps);
				closeResultSet(rs);
				closeConnection(conn);
			}

		}
		return statValue;
	}

	public List<DetailParamData> getBreakdownStatistics(String paramName,
			Date startDate, Date endDate, int intervalInMinute) {

		List<DetailParamData> detailDataList = new ArrayList<DetailParamData>();

		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		if (conn != null) {
			try {
				String sqlStatement;
				if (paramName.equals(Constants.REDO_LOG_FILE_PARAM)) {
					// Redo Log File Parameter is retrieved with a different sql
					sqlStatement = "SELECT avg(a.total_waits) value FROM dba_hist_system_event a, dba_hist_snapshot b WHERE b.snap_id = a.snap_id and a.event_name ='log file parallel write' and b.end_interval_time > to_date(?, 'dd/mm/yyyy HH24:MI:SS') and b.end_interval_time < to_date(?, 'dd/mm/yyyy HH24:MI:SS')";

				} else {
					// The other parameters can be retrieved with the same sql
					// (change the id in the where clause only)
					sqlStatement = "SELECT metric_id, metric_name, avg(average) value FROM dba_hist_sysmetric_summary WHERE metric_id =? and end_time > to_date(?, 'dd/mm/yyyy HH24:MI:SS') and end_time < to_date(?, 'dd/mm/yyyy HH24:MI:SS') GROUP BY metric_id, metric_name";
				}

				Date tempEndDate = endDate;
				Date tempStartDate = DateUtil.addMinuteToDate(tempEndDate,
						-intervalInMinute);

				while (startDate.before(tempStartDate)) {
					if (paramName.equals(Constants.REDO_LOG_FILE_PARAM)) {
						ps = conn.prepareStatement(sqlStatement);
						ps.setString(1, DateUtil.getDateString(tempStartDate));
						ps.setString(2, DateUtil.getDateString(tempEndDate));
					} else {
						ps = conn.prepareStatement(sqlStatement);
						String paramID = (String) Constants.DASHBOARD_PARAMS
								.get(paramName);

						ps.setString(1, paramID);
						ps.setString(2, DateUtil.getDateString(tempStartDate));
						ps.setString(3, DateUtil.getDateString(tempEndDate));
					}

					rs = ps.executeQuery();
					double statValue = -1;
					if (rs.next()) {
						statValue = rs.getDouble("value");
					}
					// The query to get the average value for Redo log file will
					// return 0 if no record is found. Make it to be -1 to be
					// the same as others
					if (paramName.equals(Constants.REDO_LOG_FILE_PARAM)
							&& statValue == 0) {
						statValue = -1;
					}

					DetailParamData detailData = new DetailParamData(
							(Date) tempStartDate.clone(), (Date) tempEndDate
									.clone(), statValue);
					detailDataList.add(detailData);

					tempEndDate = tempStartDate;
					tempStartDate = DateUtil.addMinuteToDate(tempEndDate,
							-intervalInMinute);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeStatement(ps);
				closeResultSet(rs);
				closeConnection(conn);
			}

		}
		return detailDataList;
	}
}
